package org.abratuhi.uradar.model;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 * Abstract class for all URadar modules. Contains the SQL part of the module, dealing with creation
 * and deleting tables from 'uradar' MySQL database.
 * <p>
 * Notice: all URadar modules must extend either URadarModule or URadarUpdateModule.
 * 
 * @author Alexei Bratuhin
 * @version 1.00
 */
public abstract class URadarModule {
	
	/**
	 * Some final static attributes for standard response message
	 */
	
	/** sent, if request was a success */
	public final static String OK = "ok";
	
	/** sent, if request failed */
	public final static String FAIL = "fail";
	
	/** sent, if module id received couldn't be resolved */
	public final static String INVALID_MODULE_ID = "invalid_module_id";
	
	/** sent, if request type couldn't be resolved **/
	public final static String INVALID_REQUEST_ID = "invalid_request_id";
	
	/** sent, if uradarid or moduleid couldn't be resolved **/
	public final static String INVALID_ID = "invalid_id";
	
	/** sent, is uradarid and uradarpassword pair received is in the database **/
	public final static String USERPASS_OK = "userpass_ok";
	
	/** sent, if uradarid received is in the database, but uradarpassword doesn't match **/
	public final static String PASS_FAIL = "pass_fail";
	
	/** sent if uradarid received isn't in the database **/
	public final static String USER_FAIL = "user_fail";
	
	/**
	 * Further module-specific attributes
	 */
	
	/** name of the module, simultaneously name of MySQL tablegroup in DB **/
	public String name;
	
	/** short description of the module **/
	String description;
	
	/** model of the module **/
	public ArrayList<URadarModuleModel> models;
	
	/** module specific parameters **/
	Properties props; 
	
	/**
	 * Constructor.
	 * <p>
	 * Notice: doesn't create MySQL tables by itself - createTables() must be called. See existing module implementations for examples.
	 * @param name			-	name of the module, mostly lower-case
	 * @param description	-	short description of the module
	 * @param props			-	module-specific properties
	 * @param connection	-	from URadar inherited connection to MySQL DB
	 */
	public URadarModule(String name, String description, Properties props){
		// perform check of parameters
		if(name==null || name==""){
			System.out.println("URadarModule "+name+":\tbad value of parameter name");
		}
		if(props==null){
			System.out.println("URadarModule "+name+":\tbad value of parameter props");
		}
		// init attributes/values
		this.name = name;
		this.description = description;
		this.props = props;
	}
	
	protected Connection openConnection() {
		try {
			// load properties file
			//FileInputStream fis;
			//Properties props = new Properties();
			//try {
			//	fis = new FileInputStream("uradar.properties");
			//	props.load(fis);
			//} catch (FileNotFoundException e1) {
			//	e1.printStackTrace();
			//} catch (IOException e) {
			//	e.printStackTrace();
			//}
			// read properties
			String host = props.getProperty("mysql.host");
			String db = props.getProperty("mysql.db");
			String user = props.getProperty("mysql.user");
			String passwd = props.getProperty("mysql.passwd");
			// create connection
			Class.forName ("com.mysql.jdbc.Driver").newInstance();
			return DriverManager.getConnection("jdbc:mysql://"+host+"/"+db, user, passwd);
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("URadar:\tUnable to connect to MySQL DB under specified .properties");
			System.out.println("mysql.host = "+props.getProperty("mysql.host"));
			System.out.println("mysql.db = "+props.getProperty("mysql.db"));
			System.out.println("mysql.user = "+props.getProperty("mysql.user"));
			System.out.println("mysql.passwd = "+props.getProperty("mysql.passwd"));
			System.exit(-1);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	protected void closeConnection(Connection connection){
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Method, that sorts out the type of the request, then calls the corresponding function and returns result to URadar.
	 * <p>
	 * Notice: usually, when a module cannot fulfill the request itself, request is forwarded to superclass.
	 * @param props		Request properties gained from HTTP GET query
	 * @return			Response to Request, either in XML format or in String, which is then one of predefined messages (e.g. FAIL, if request couldn't be fulfilled) 
	 */
	public abstract String proceedRequest(Properties props);
	
	/**
	 * Resolve moduleID to U(niversal)RadarID.
	 * @param reqprops		HTTP GET query
	 * @return				<ul><li>U(niversal)RadarID, if moduleID found in DB</li>
	 * 						<li>module-specific response in rest of cases</li></ul>
	 */
	public abstract String resolveModuleID(Properties reqprops);
	
	/**
	 * Set model of the module - generally, names and types of fields in MySQL table.
	 * @param model			module's table'(s') model(s)
	 */
	@SuppressWarnings("unchecked")
	public void setModel(ArrayList<URadarModuleModel> models){
		// set model
		this.models = (ArrayList<URadarModuleModel>) models.clone();
		// generate sql queries for each module
		for (int i=0; i<this.models.size(); i++){
			this.models.get(i).generateCreateSQLQuery(this.name);
			this.models.get(i).generateDeleteSQLQuery(this.name);
		}
	}
	
	/**
	 * Get model of the module, which represents an array of special objects, representing structure of SQL tables of module.
	 * @return		module's table'(s') model(s)
	 */
	public ArrayList<URadarModuleModel> getModel(){
		return models;
	}
	
	/**
	 * Create MySQL tables in DB using automatically generated SQL queries in module's models.
	 * @return		<ul><li>true, if the creation of tables was a success</li>
	 * 				<li>false, otherwise</li></ul>
	 */
	public boolean createTables(){
		if(models != null){
			for(int i=0; i<models.size(); i++){
				try{
					// open connection
					Connection connection = openConnection();
					// create statement
					Statement stmt = connection.createStatement();
					// System.out
					System.out.println("Executing SQL query:\t"+models.get(i).sql_query_create);
					// create tables in DB
					for(int j=0; j<models.get(i).sql_query_create.size(); j++){
						stmt.executeUpdate(models.get(i).sql_query_create.get(j));
					}
					// close statement, garbage collector is not to be relied upon
					stmt.close();
					// close connection
					closeConnection(connection);
				} catch (SQLException e){
					e.printStackTrace();
				}
			}
			// return
			return true;
		}
		// case things went wrong
		return false;
	}
	
	/**
	 * Checks whether all those tables, needed for functioning of the module, are present in MySQL DB. 
	 * The list of tables to create is represented with arraylist of URadarModuleModels.
	 * @return		<ul><li>true, if all tables are present</li>
	 * 				<li>false, otherwise</li></ul>
	 */
	public boolean checkTables(){
		// init return/response variable
		boolean result = false;
		if(models != null){
			for(int i=0; i<models.size(); i++){
				try {
					// open connection
					Connection connection = openConnection();
					// init statement
					Statement stmt = connection.createStatement();
					// check, whether tables are present in db
					DatabaseMetaData md = connection.getMetaData();
					ResultSet modulename = md.getTables(null, null, models.get(i).name, null);
					// check whether all tables are already in db
					if(modulename.first()){
						result = true;
					}
					else{
						result = false;
						return result;
					}
					// close statement, garbage collector is not to be relied upon
					stmt.close();
					
					// close connection
					closeConnection(connection);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		// return
		return result;
	}
	
	/**
	 * Delete MySQL tables in DB  using automatically generated SQL queries in module's models.
	 * @return		<ul><li>true, if deletion successful</li>
	 * 				<li>false, otherwise</li></ul>
	 */
	public boolean deleteTables(){
		if(models != null){
			for(int i=0; i<models.size(); i++){
				try{
					// open connection
					Connection connection = openConnection();
					// create statement
					Statement stmt = connection.createStatement();
					// create tables in DB
					for(int j=0; j<models.get(i).sql_query_delete.size(); j++){
						stmt.executeUpdate(models.get(i).sql_query_delete.get(j));
					}
					// close statement, garbage collector is not to be relied upon
					stmt.close();
					// close connection
					closeConnection(connection);
				} catch (SQLException e){
					e.printStackTrace();
				}
			}
			// return
			return true;
		}
		// case things went wrong
		return false;
	}
}
